using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Data;

/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：销售退货主表接口实现
*│　作    者：李宝
*│　版    本：1.0 模板代码自动生成
*│　创建时间：2019-01-05 16:55:24
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： Usido.CRM.Repository
*│　类    名： SalesReturnOrderRepository
*└──────────────────────────────────────────────────────────────┘
*/

using Usido.CRM.Core.DbHelper;
using Usido.CRM.Core.Extensions;
using Usido.CRM.Core.Options;
using Usido.CRM.Core.Repository;
using Usido.CRM.Core.ToolHelper;
using Usido.CRM.Dto;
using Usido.CRM.IRepository;
using Usido.CRM.Models;

namespace Usido.CRM.Repository
{
    public class SalesReturnOrderRepository : BaseRepository<SalesReturnOrder, int>, ISalesReturnOrderRepository
    {
        private readonly ILog logger = LogManager.GetLogger(nameof(SalesReturnOrderRepository));

        public SalesReturnOrderRepository()
        {
            _dbConnection = ConnectionFactory.CreateConnection(DbOptionData.DbType, DbOptionData.ConnectionString);
        }

        public int AddSalesReturn(SalesReturnOrder order, List<SalesReturnInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlAddSalesReturn = @"
INSERT INTO dbo.SalesReturnOrder(OrderCode,UserId,CreateTime,PutTime,State,Remark,OriginalSalesOrderId)
VALUES(@OrderCode,@UserId,getdate(),@PutTime,@State,@Remark,@OriginalSalesOrderId) SELECT @@IDENTITY";

            string sqlAddSalesReturnInfo = @"
INSERT dbo.SalesReturnInfo(ProductId,BatchNumber,Quantity,Price,OriginalSalesInforId,OrderId,DateExpiry)
VALUES(@ProductId,@BatchNumber,@Quantity,@Price,@OriginalSalesInforId,@OrderId,@DateExpiry)";
            try
            {
                order.OrderCode = Tools.GetOrderCode(2);
                int orderId = _dbConnection.ExecuteScalar<int>(sqlAddSalesReturn, order, transaction);
                if (orderId <= 0)
                {
                    transaction.Rollback();
                    return -1;
                }

                foreach (var item in orderInfoList)
                {
                    item.OrderId = orderId;
                    _dbConnection.Execute(sqlAddSalesReturnInfo, item, transaction);
                }

                transaction.Commit();

                return orderId;
            }
            catch (Exception ex)
            {
                logger.Error("添加销售退货信息时出错", ex);
                transaction.Rollback();

                return -1;
            }
        }

        public bool DeleteSalesReturn(int id)
        {
            string sql = @"DELETE dbo.SalesReturnOrder WHERE Id = @OrderId;
DELETE dbo.SalesReturnInfo WHERE OrderId = @OrderId;";

            return _dbConnection.Execute(sql, new { OrderId = id }) > 0;
        }

        public List<SalesReturnInfoDto> GetSalesReturnInfoList(int orderId)
        {
            string sql = @"SELECT * FROM (
	SELECT A.*,
		   B.ProductName,
		   B.ProductCode,
		   B.Specification,
		   B.MarketingUnit,
		   B.ManufacturingEnterprise,
		   B.RetailPrice,
		   B.MemberDiscount,
		   CONVERT(DECIMAL(18, 2), B.RetailPrice * ISNULL(B.MemberDiscount, 100) / 100) AS MemberPrice ,
		   C.Quantity - dbo.P_GetSalesQuantityReturnByReturnId(A.Id) AS QuantityReturned
	FROM dbo.SalesReturnInfo AS A
	INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
	INNER JOIN dbo.SalesInfo AS C ON A.OriginalSalesInforId = C.Id
	WHERE A.OrderId = @OrderId
)T ";

            return _dbConnection.Query<SalesReturnInfoDto>(sql, new { OrderId = orderId }).AsList();
        }

        public List<SalesReturnOrderDto> GetSalesReturnList(string where = "")
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*,B.UserName FROM dbo.SalesReturnOrder AS A
	INNER JOIN dbo.SysUser AS B ON A.UserId = B.Id
)T ";
            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }

            return _dbConnection.Query<SalesReturnOrderDto>(sql).AsList();
        }

        public bool OutStore(int orderId, int userId)
        {
            var result = _dbConnection.ExecuteScalar<int>("P_SalesReturnOrderPutStorage", new { UserId = userId, OrderId = orderId }, commandType: CommandType.StoredProcedure);

            return result == 1;
        }

        public bool UpdateSalesReturn(SalesReturnOrder order, List<SalesReturnInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlUpdateSales = @"UPDATE dbo.SalesReturnOrder SET Remark = Remark WHERE Id = @Id";
            string sqlDeleteSalesInfo = @"DELETE dbo.SalesReturnInfo WHERE OrderId = @OrderId";
            string sqlUpdateSalesInfo = @"
INSERT INTO dbo.SalesReturnInfo(ProductId,BatchNumber,Quantity,Price,OriginalSalesInforId,OrderId,DateExpiry)
VALUES(@ProductId,@BatchNumber,@Quantity,@Price,@OriginalSalesInforId,@OrderId,@DateExpiry)";
            try
            {
                _dbConnection.Execute(sqlUpdateSales, order, transaction);
                _dbConnection.Execute(sqlDeleteSalesInfo, new { OrderId = order.Id }, transaction);

                foreach (var item in orderInfoList)
                {
                    item.OrderId = order.Id;
                    _dbConnection.Execute(sqlUpdateSalesInfo, item, transaction);
                }

                transaction.Commit();

                return true;
            }
            catch (Exception ex)
            {
                logger.Error("修改销售退货信息时出错", ex);
                transaction.Rollback();

                return false;
            }
        }
    }
}